/**
* @ClassName: ParseExcelToMysqlDatabase
* @Description: 
* @author YuYang
* @date 2013-6-18 下午6:33:33
*/
package com.yang.test;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.demodis.dao.GroupDaoI;
import com.demodis.dao.impl.GroupDaoImpl;
import com.demodis.domain.TblGroup;
import com.demodis.domain.TblUser;
import com.demodis.service.UserServiceI;
import com.demodis.service.impl.UserServiceImpl;
import com.demodis.util.Encrypt;

public class ParseExcelToMysqlDatabase {
	static List<TblUser> userList = new ArrayList<TblUser>();
	static GroupDaoI groupDao = new GroupDaoImpl();
	static UserServiceI userService = new UserServiceImpl();
	
	static Map<String, Integer> groupMap = new HashMap<String, Integer>();

	public static void main(String[] args) throws Exception {
		InputStream is = new FileInputStream("F:\\全集团3.xls");
		POIFSFileSystem fs = new POIFSFileSystem(is);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		
		
		TblUser user = null;
		
		for(int i = 1; i <= sheet.getLastRowNum(); i++) {
			HSSFRow row = sheet.getRow(i);
			user = new TblUser();
			user.setUserRealName(row.getCell(2).getStringCellValue());
			user.setUsername(row.getCell(3).getStringCellValue());
			user.setGroupName(row.getCell(5).getStringCellValue());
			
			//密码取生分证后六位  并加密
			String password = row.getCell(6).getStringCellValue();
			//password = password.substring(password.length() - 6);
			user.setPassword(password);
			//user.setPassword(Encrypt.e(password));
			userList.add(user);
			
			//System.out.println(JSON.toJSON(user));
			
		}
		for(TblUser u : userList) {
			if(u.getPassword().length() != 18) {
				continue;
			}
			StringBuffer str = new StringBuffer();
			str.append(u.getUserRealName())
			   .append(" ").append(u.getPassword().substring(6, 10))
			   .append(" ").append(u.getPassword().substring(10, 12))
			   .append(" ").append(u.getPassword().substring(12, 14))
			   .append(" ").append("今天是"+u.getGroupName()+u.getUserRealName()+"的生日")
			   .append(" ").append("True 00000000000");
			
			System.out.println(str.toString());
		}
		//ParseExcelToMysqlDatabase.saveGroup();
		/*ParseExcelToMysqlDatabase.getGroupList();
		for(TblUser u : userList) {
			u.setGroupId(groupMap.get(u.getGroupName()));
			userService.save(u);
		}*/
		//ParseExcelToMysqlDatabase.saveGroup();
		/*for(TblGroup group : groupList) {
				System.out.println(group.getGroupName());
		}*/
		
	}
	
	public static void saveGroup() {
		Map<String, String> tempMap= new HashMap<String, String>();
		for(TblUser user : userList) {
			if(!tempMap.containsKey(user.getGroupName())) {
				tempMap.put(user.getGroupName(), null);
				TblGroup group = new TblGroup();
				group.setGroupName(user.getGroupName());
				group.setShowSort(0);
				groupDao.save(group);
			}
		}
		
	}
	
	public static void getGroupList() {
		List<TblGroup> groupList  = groupDao.getGroup("");
		for(TblGroup group : groupList) {
			if(!groupMap.containsKey(group.getGroupName())) {
				groupMap.put(group.getGroupName(), group.getGroupId());
			}
		}
	}
}
